# C conc, 1.21 Si 1.89 Mn2.04 P0.12 S0.06 Cr4.24 Mo1.27 Ni4.38 Cu0.49 V0.55 Nb0.24 Al0.32
# C    k, 1.06 Si 1.04 Mn1.03 P1.06 S1.06 Cr0.99 Mo0.99 Ni1.01 Cu0.95 V1.04 Nb1.10 Al1.03
from datetime import date
import getpath
import os
import logging
import re
import config_json
import copy
import numpy
import time
from PyQt5.QtCore import QDateTime, QTime
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
import sqlalchemy
from sqlalchemy import or_, and_, func
from PyQt5 import QtWidgets
from PyQt5.QtCore import QDateTime, QTime, Qt
from PyQt5.QtWidgets import QStyle
import myglobal
import openpyxl
import ncsmodel
class MyDb():
    def __init__(self,mainw):
        self.mainw=mainw
        self.inited=self.init_db()
        self.doublefmt="%.6f"
        self.properties = [
            {"ele":None,"delegate":"date",'width':130,'attr': "SampleId",'visible':True, 'header': "time",'text': "%Y-%m-%d %H:%M:%S"},
            {"ele":None,"delegate":None,'width':130,'attr': "SampleName",'visible':True,  'header': "id"},
            {"ele":None,"delegate":"doubleEdit",'width':80,'attr': "SampleWeight",'visible':True,  'header': "mass"},
            {"ele":None,"delegate":"edit",'width':80,'attr': "beizhu",'visible':True,  'header': "备注"},
            {"ele":("C","L"),"delegate":"double",'width':80,'attr': "Content10",'visible':True,'header': "LC"},
            {"ele":("C","H"),"delegate":"double",'width':80,'attr': "Content20",'visible':True,'header': "HC"},
            {"ele":("S","L"),"delegate":"double",'width':80,'attr': "Content30",'visible':True,'header': "LS"},
            {"ele":("S","H"),"delegate":"double",'width':80,'attr': "Content40",'visible':True,'header': "HS"},
            # {"ele":None,"delegate":self.m_doubleDeleget,'width':80,'attr': "Content50",'visible':False,'header': "Content50",'mode': "Read Only"},
            # {"ele":None,"delegate":self.m_doubleDeleget,'width':80,'attr': "Content60",'visible':False,'header': "Content60",'mode': "Read Only"},
        ]
    def output_xlsx(self, filepath):

        n = len(self.mainw.objects2)
        if n == 0:
            return
        filepath = filepath + ".xlsx"
        # 1. 创建文件对象（指定文件名，模式，编码方式）a模式 为 下次写入在这次的下一行
        wb = openpyxl.Workbook()
        # 3. 构建列表头
        row = ["id", "m","time","备注"]
        for f in self.properties:
            if f["visible"] and f["ele"]!=None:
                row.append(f["ele"][0])
        ws = wb.active
        ws.append(row)
        for row_idx in range(n):
            record = self.mainw.objects2[row_idx]
            row = [record.SampleName,record.SampleWeight,record.SampleId,record.beizhu]
            for f in self.properties:
                if f["visible"] and f["ele"]!=None:
                    row.append(getattr(record,f["attr"]))
            ws.append(row)
        ffbh = self.mainw.ui.cmb_ffbh.text()
        syy = self.mainw.ui.cmb_syy.text()
        ysry = self.mainw.ui.cmb_ysry.text()
        yqbh = config_json.setting["yqbh"]
        syyqm = self.mainw.ui.cmb_syyqm.text()
        row = ["ffbh&" + ffbh, "", "", "", ""]
        ws.append(row)
        row = ["syy&" + syy, "", "", "", ""]
        ws.append(row)
        row = ["ysry&" + ysry, "", "", "", ""]
        ws.append(row)
        row = ["yqbh&" + yqbh, "", "", "", ""]
        ws.append(row)
        row = ["syyqm&" + syyqm, "", "", "", ""]
        ws.append(row)
        ff2= self.mainw.ui.cmb_ff2.text()
        js2= self.mainw.ui.cmb_js2.text()
        if config_json.setting["ejff"]==1 and ff2 != "" and js2 != "":
            ws.append(["ejff&" + ff2,"", "", "", ""])  # todo
            ws.append(
                ["ejfxjs&" + js2, "", "", "", ""])        
        ws.column_dimensions['A'].width = 30.0
        wb.save(filepath)
        self.mainw.ui.textEdit.insertPlainText(filepath + "\n")
        scrollbar = self.mainw.ui.textEdit.verticalScrollBar()
        scrollbar.setSliderPosition(scrollbar.maximum())
    def init_db(self):
        logging.info(config_json.setting['database'])
        if config_json.setting['database'] == None:
            return False
        if not os.path.exists(config_json.setting['database']):
            return False
        try:
            # self.Base = automap_base()
            filedb = config_json.setting['database']
            logging.info(filedb)
            self.engine = create_engine('sqlite:///'+filedb, echo=True)
            # reflect the tables
            # self.Base.prepare(autoload_with=self.engine)
            Session = sessionmaker(bind=self.engine,autoflush=False)
            self.session = Session()
            # logging.info(dir(self.Base.classes))
            return True
        except sqlalchemy.exc.OperationalError as e:
            logging.info(e)
            return False
        except sqlalchemy.exc.DatabaseError as e:
            logging.info("DatabaseError")
            logging.info(e)
            return False
    def close(self):
        try:
            self.session.close()
        except AttributeError:
            pass
        pass
    def getData(self, db_str, de_str):
        logging.info([db_str,de_str])
        cmd = "select * from result where sampleid between '" + \
            db_str + "' and '" + de_str + "' "
        cursor = self.session.execute(cmd)  # 执行sql语句
        raw = cursor.fetchall()  # 返回结果行 或使用 #raw = cursor.fetchall()
        for one in raw:
            logging.info(one)

    def getDataObjects(self, db_str, de_str):
        task_filter = {
            and_(
                ncsmodel.Result.SampleId > db_str,
                ncsmodel.Result.SampleId < de_str
            )
        }
        ncsmodel.Result.beizhu=""
        ncsmodel.Result.num=0
        contacts = self.session.query(
            ncsmodel.Result).filter(*task_filter).all()
        return contacts
    def getDataIds(self,ids):
        task_filter = {
            ncsmodel.Result.SampleId.in_(ids)
        }
        contacts = self.session.query(
            ncsmodel.Result).filter(*task_filter).all()
        return contacts
    def getDataDays(self, v):
        logging.info(v)
        de = QDateTime.currentDateTime()
        de.setTime(QTime(23, 59, 59))
        de_str = de.toString("yyyy-MM-dd hh:mm:ss")
        db = de.addDays(-v)
        db.setTime(QTime(0, 0, 0))
        db_str = db.toString("yyyy-MM-dd hh:mm:ss")
        objs = self.getDataObjects(db_str, de_str)
        num=0
        for obj in objs:
            obj.num=num
            # logging.info(obj.SampleId)
            # obj.SampleId=obj.SampleId.strftime("%Y-%m-%d %H:%M:%S")
            num+=1
        return objs

    def test1(self):
        db = QDateTime.currentDateTime()
        db.setTime(QTime(0, 0, 0))
        db_str = db.toString("yyyy-MM-dd hh:mm:ss")
        de = QDateTime.currentDateTime()
        de.setTime(QTime(23, 59, 59))
        de_str = de.toString("yyyy-MM-dd hh:mm:ss")
        logging.info(self.getData(db_str, de_str))


if __name__ == "__main__":
    config_json.read_setting()
    file = os.path.join(getpath.getpath(), "data.db")
    config_json.setting['database'] = file
    db = MyDb()
    # db.getData()
    logging.info(db.getDataDays(200))
